var pool = require('../modules/db_mysql').pool;
var async = require('async');

var global_data = {};
var global_connection;
var delEssayTag = function(callback){
    var data = global_data;
    var delSql = "delete from essay_tag where essay_id="+data.id;
    pool.getConnection(function(err, connection) {
        connection.query(delSql, function(err, result, fields){
            connection.release()
            if(err){
                callback(err, "");
            }else{
                //console.log("count result:", result);
                callback(null, result);
            }
        });    
    })            
};

var insertEssayTag = function(callback, params){
    var data = global_data;
    if(data.id == 0)
    {
        data.id = params["insertOrUpdate"].insertId;
    }
    if(data.tags.length==0){
        callback(null, null);
    }
    else{
        var insertSql = "insert into essay_tag values ";
        for (var i = 0; i < data.tags.length; i++) {
            insertSql += i>0?",":"";
            insertSql += "(" + data.id + "," + data.tags[i].id + ")";
        };
        pool.getConnection(function(err, connection) {
            connection.query(insertSql, function(err, result, fields){
                connection.release();
                if(err){
                    callback(err, "");
                }else{
                    //console.log("count result:", result);
                    callback(null, result);
                }
            });   
        });    
    }         
};

module.exports.save = function save(data, callback) {
    pool.getConnection(function(err, connection) {     
        global_data = data;
        async.auto({
            insertOrUpdate: function(callback){
                var sql = "";
                if(data.id == 0){
                    sql = "insert into essay(`title`, `content`, `category_id`, `is_star`, `status`, `order`, `post_time`, `last_modified_time`) "
                        + " values (?,?,?,?, 1, 99, ?, now())";
                }     
                else{
                    sql = "update essay "
                        + " set title=?, content=?, category_id=?,"
                        + " is_star=?, post_time=?, last_modified_time=now() "            
                        + " where id="+data.id

                }
                //console.log(sql)
                connection.query(sql, [data.title, data.content, data.category_id, data.is_star, data.post_time], function(err, result, fields){
                    if(err){
                        callback(err, "");
                    }else{
                        console.log("insert result:", result);
                        callback(null, result);
                    }
                });
            },
            delEssayTag: delEssayTag,
            insertEssayTag: ["insertOrUpdate", "delEssayTag", insertEssayTag]
        }, function(err, params){
            connection.release();
            if(err){
                return callback(err, null);
            }            
            
            if(data.id == 0)
            {
                data.id = params["insertOrUpdate"].insertId;
            }
            return callback(err, data.id);            
        });
    });
};

module.exports.search = function search(condition, callback) {
    condition.findOne = condition.findOne || false;

    var selectSql = "select e.id, e.title, e.content, e.is_star, e.love, e.status, e.post_time, e.last_modified_time "            
            + "     , category_id, c.title as category_title ";
    //if(condition.findOne){
        //selectSql += ' , concat("[", group_concat(concat("{\\"id\\":",t.id,", \\"title\\":\\"", t.title, "\\",\\"color\\":\\"",t.color,"\\"}") separator ","), "]") as tags ';
    //}else{
        selectSql += " , ifnull(group_concat(t.id separator ';'),'') as tag_ids "
            + "     , ifnull(group_concat(t.title separator ';'),'') as tag_titles "
            + "     , ifnull(group_concat(t.color separator ';'),'') as tag_colors ";
    //}

    var tableSql = "from essay e "
            + "left join category c on e.category_id=c.id "
            + "left join essay_tag et on e.id=et.essay_id "
            + "left join tag t on et.tag_id=t.id ";

    var whereSql = "";
    if(condition.searchCriteria.id && condition.searchCriteria.id != 0){
        whereSql += " where e.id=" + condition.searchCriteria.id
    }

    var orderSql = " order by e.post_time desc ";
        orderSql += " , e.id desc, e.last_modified_time desc, e.created_time desc";

    var limitSql = "";
    if(condition.pagingMetaData){
        condition.pagingMetaData.pageIndex == condition.pagingMetaData.pageIndex<=0?0:condition.pagingMetaData.pageIndex;
        var start = condition.pagingMetaData.pageIndex*condition.pagingMetaData.pageSize
        limitSql = " limit " + start + ", " + condition.pagingMetaData.pageSize + " ";
    }

    var groupSql = " group by e.id, e.title, e.content, e.is_star, e.love, e.status, e.post_time, e.last_modified_time, category_id, category_title";

    var querySql = selectSql + tableSql + whereSql + groupSql + orderSql + limitSql;
    var countSql = "select count(distinct e.id) as count " + tableSql + whereSql + "; ";
    console.log("querySql:", querySql);

    pool.getConnection(function(err, connection) {        
        async.auto({
            query: function(callback){
                connection.query(querySql, function(err, result, fields){
                    if(err){
                        callback(err, "");
                    }else{
                        //console.log("query result:", result);
                        callback(null, result);
                    }
                });
            },
            count: function(callback){
                if(condition.findOne){
                    callback(null, [{count:1}]);
                }
                else{
                    connection.query(countSql, function(err, result, fields){
                        if(err){
                            callback(err, "");
                        }else{
                            //console.log("count result:", result);
                            callback(null, result);
                        }
                    });
                }
            }
        }, function(err, params){
            connection.release();
            if(err){
                return callback(err, null);
            }            

            var result = [], total = 0;
            if(params["query"]){
                result = params["query"];
            }
            if(params["count"] && params["count"].length>0){
                total = params["count"][0].count;
            }
            console.log("result:", result);
            //console.log("total:", total);            
            return callback(err, {result:result, total:total});
            
        });
    });

};